﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace WindowsFormsApplication2.Model
{
    class Karyawan
    {
        
        int id;
        string nama;
        string alamat;
        string no_telp;
        string no_ktp;
        bool is_male;
        DateTime tgl_lahir;
        string id_status;
        string id_agama;
        string id_gol_darah;
        DateTime tgl_masuk;
        DateTime tgl_keluar;
        int id_cabang;

        public Karyawan()
        {
            
        }

        public Karyawan(string _Nama)
        {
            Database db = new Database();
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            string sql = "SELECT * FROM karyawan WHERE nama = '" + _Nama + "'";

            db.Open();
            ds = db.ExecuteQuery(sql);
            dt = ds.Tables[0];
            DataRow dr = dt.Rows[0];

            id = (int)dr[0];
            nama = (string)dr[1];
            alamat = (string)dr[2];
            no_telp = (string)dr[3];
            no_ktp = (string)dr[4];
            is_male = (bool)dr[5];
            tgl_lahir = (DateTime)dr[6];
            id_status = (string)dr[7];
            id_agama = (string)dr[8];
            id_gol_darah = (string)dr[9];
            tgl_masuk = (DateTime)dr[10];
            tgl_keluar = (dr[11] != System.DBNull.Value?(DateTime)dr[11]:new DateTime(0));
            id_cabang = (int)dr[12];
            
            db.Close();
        }

        public static DataTable DaftarKaryawan(int _Cabang)
        {
            Database db = new Database();
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            string sql = "SELECT * FROM karyawan WHERE id_cabang = " + _Cabang.ToString();

            db.Open();
            ds = db.ExecuteQuery(sql);
            dt = ds.Tables[0];
            db.Close();

            return dt;
        }

        public static ArrayList DaftarKaryawanIdNama(int _Cabang)
        {
            ArrayList a = new ArrayList();
            Database db = new Database();
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            object[] o = new object[2];
            string sql = "SELECT * FROM karyawan WHERE id_cabang = " + _Cabang.ToString();

            db.Open();
            ds = db.ExecuteQuery(sql);
            dt = ds.Tables[0];
            db.Close();
            DataRow dr;

            for (int i = 0; i < dt.Rows.Count; ++i)
            {
                dr = dt.Rows[i];

                o[0] = (int)dr[0];
                o[1] = (string)dr[1];
                a.Add(o.Clone());
            }

            return a;
        }

        public static DataTable DaftarKaryawan(int _Cabang, string _Keahlian)
        {
            Database db = new Database();
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            string sql = "SELECT karyawan.id, nama FROM karyawan INNER JOIN keahlian ON karyawan.id = keahlian.id_karyawan WHERE id_cabang = " + _Cabang.ToString() + " AND id_layanan = '" + _Keahlian + "'";

            db.Open();
            ds = db.ExecuteQuery(sql);
            dt = ds.Tables[0];
            db.Close();

            return dt;
        }

        public static ArrayList DaftarKaryawanArrayList(int _Cabang, string _Keahlian)
        {
            ArrayList a = new ArrayList();
            Database db = new Database();
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            object[] o = new object[2];
            string sql = "SELECT karyawan.id, karyawan.nama FROM (karyawan INNER JOIN keahlian ON karyawan.id = keahlian.id_karyawan) INNER JOIN layanan ON id_layanan = layanan.id WHERE id_cabang = " + _Cabang.ToString() + " AND layanan.nama = '" + _Keahlian + "'";

            db.Open();
            ds = db.ExecuteQuery(sql);
            dt = ds.Tables[0];
            db.Close();
            DataRow dr;

            for (int i = 0; i < dt.Rows.Count; ++i)
            {
                dr = dt.Rows[i];

                o[0] = (int)dr[0];
                o[1] = (string)dr[1];
                a.Add(o.Clone());
            }

            return a;
        }

        public void AddKaryawan()
        {
            Database db = new Database();
            string sql = "INSERT INTO karyawan(nama, alamat, no_telp, no_ktp, is_male, tgl_lahir, id_status, id_agama, id_gol_darah, tgl_masuk, tgl_keluar, id_cabang) VALUES ('" + nama + "','" + alamat +
                    "','" + no_telp + "','" + no_ktp + "'," + is_male.ToString() + ",'" + tgl_lahir.ToString() + "','" + id_status + "','" + id_agama + "','" + id_gol_darah + "','" + tgl_masuk.ToString() + "','" + tgl_keluar.ToString() + "','" + id_cabang.ToString() + "')";

            db.Open();
            db.ExecuteNonQuery(sql);
            db.Close();

        }

        public void UpdateKaryawan()
        {
        }

        public void DeleteKaryawan()
        {
        }

    }
}
